#!/usr/bin/env python3
"""
DataPlexus CSV配置字段迁移脚本
添加CSV相关配置字段到dp_sync_tasks表
"""

import pymysql
import sys
import os

# 数据库配置
DB_CONFIG = {
    'host': '192.168.37.11',
    'port': 3306,
    'user': 'root',
    'password': '123456',
    'database': 'data_plexus',
    'charset': 'utf8mb4'
}

# 要添加的字段定义
CSV_FIELDS = [
    {
        'name': 'csv_delimiter',
        'definition': "VARCHAR(10) DEFAULT ',' COMMENT 'CSV分隔符'",
        'after': 'target_path'
    },
    {
        'name': 'csv_quote_char',
        'definition': "VARCHAR(5) DEFAULT '\"' COMMENT 'CSV引用字符'",
        'after': 'csv_delimiter'
    },
    {
        'name': 'csv_escape_char',
        'definition': "VARCHAR(5) DEFAULT '\\\\' COMMENT 'CSV转义字符'",
        'after': 'csv_quote_char'
    },
    {
        'name': 'csv_line_terminator',
        'definition': "VARCHAR(10) DEFAULT '\\n' COMMENT 'CSV行终止符'",
        'after': 'csv_escape_char'
    },
    {
        'name': 'csv_encoding',
        'definition': "VARCHAR(20) DEFAULT 'utf-8' COMMENT 'CSV文件编码'",
        'after': 'csv_line_terminator'
    },
    {
        'name': 'csv_include_header',
        'definition': "BOOLEAN DEFAULT TRUE COMMENT '是否包含表头'",
        'after': 'csv_encoding'
    },
    {
        'name': 'csv_null_value',
        'definition': "VARCHAR(20) DEFAULT '' COMMENT 'NULL值表示方式'",
        'after': 'csv_include_header'
    },
    {
        'name': 'csv_date_format',
        'definition': "VARCHAR(50) DEFAULT '%%Y-%%m-%%d %%H:%%M:%%S' COMMENT '日期时间格式'",
        'after': 'csv_null_value'
    }
]

def check_column_exists(cursor, table_name, column_name):
    """检查字段是否存在"""
    cursor.execute("""
        SELECT COUNT(*) 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = %s 
          AND TABLE_NAME = %s 
          AND COLUMN_NAME = %s
    """, (DB_CONFIG['database'], table_name, column_name))
    
    return cursor.fetchone()[0] > 0

def add_csv_fields():
    """添加CSV配置字段"""
    connection = None
    try:
        # 连接数据库
        print("连接数据库...")
        connection = pymysql.connect(**DB_CONFIG)
        cursor = connection.cursor()
        
        print(f"成功连接到数据库: {DB_CONFIG['host']}:{DB_CONFIG['port']}/{DB_CONFIG['database']}")
        
        # 检查表是否存在
        cursor.execute("""
            SELECT COUNT(*) 
            FROM INFORMATION_SCHEMA.TABLES 
            WHERE TABLE_SCHEMA = %s 
              AND TABLE_NAME = 'dp_sync_tasks'
        """, (DB_CONFIG['database'],))
        
        if cursor.fetchone()[0] == 0:
            print("错误: dp_sync_tasks表不存在")
            return False
        
        print("开始添加CSV配置字段...")
        
        # 逐个添加字段
        for field in CSV_FIELDS:
            field_name = field['name']
            field_definition = field['definition']
            after_field = field['after']
            
            # 检查字段是否已存在
            if check_column_exists(cursor, 'dp_sync_tasks', field_name):
                print(f"字段 {field_name} 已存在，跳过")
                continue
            
            # 添加字段
            sql = f"ALTER TABLE dp_sync_tasks ADD COLUMN {field_name} {field_definition} AFTER {after_field}"
            print(f"执行: {sql}")
            
            try:
                cursor.execute(sql)
                print(f"✓ 成功添加字段: {field_name}")
            except Exception as e:
                print(f"✗ 添加字段 {field_name} 失败: {e}")
                return False
        
        # 提交事务
        connection.commit()
        print("所有字段添加完成")
        
        # 验证字段
        print("\n验证添加的字段:")
        cursor.execute("""
            SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = %s
              AND TABLE_NAME = 'dp_sync_tasks'
              AND COLUMN_NAME LIKE 'csv_%%'
            ORDER BY ORDINAL_POSITION
        """, (DB_CONFIG['database'],))
        
        results = cursor.fetchall()
        if results:
            print("字段名\t\t\t数据类型\t默认值\t\t注释")
            print("-" * 80)
            for row in results:
                column_name, data_type, is_nullable, default_value, comment = row
                print(f"{column_name:<20}\t{data_type}\t{default_value or 'NULL':<15}\t{comment}")
        else:
            print("未找到CSV配置字段")
            return False
        
        # 更新现有CSV任务的默认配置
        print("\n更新现有CSV任务的默认配置...")
        cursor.execute("""
            UPDATE dp_sync_tasks 
            SET 
                csv_delimiter = ',',
                csv_quote_char = '"',
                csv_escape_char = '\\\\',
                csv_line_terminator = '\\n',
                csv_encoding = 'utf-8',
                csv_include_header = TRUE,
                csv_null_value = '',
                csv_date_format = '%%Y-%%m-%%d %%H:%%M:%%S'
            WHERE target_type = 'csv' 
              AND (csv_delimiter IS NULL OR csv_delimiter = '')
        """)
        
        affected_rows = cursor.rowcount
        connection.commit()
        print(f"更新了 {affected_rows} 个CSV任务的配置")
        
        print("\n✓ 数据库迁移完成!")
        return True
        
    except Exception as e:
        print(f"数据库迁移失败: {e}")
        if connection:
            connection.rollback()
        return False
        
    finally:
        if connection:
            connection.close()
            print("数据库连接已关闭")

if __name__ == "__main__":
    print("DataPlexus CSV配置字段迁移脚本")
    print("=" * 50)
    
    success = add_csv_fields()
    
    if success:
        print("\n迁移成功! 现在可以重启后端服务。")
        sys.exit(0)
    else:
        print("\n迁移失败! 请检查错误信息。")
        sys.exit(1)
